﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Word = Microsoft.Office.Interop.Word;
//using Microsoft.Office.Core;
using System.Threading;

namespace SqlServerAutoToWordTable
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            Control.CheckForIllegalCrossThreadCalls = false;//关闭跨线程修改控件检查
            //PictureBox.CheckForIllegalCrossThreadCalls = false;
        }
        public static string connectionString;

        /// <summary>
        /// 开始转换
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            SqlConvert.Enabled = false;
            titleText.Visible = true;
            titleText.Text = "开始生成,数据表越多生成越慢，请耐心等待。。。";
            string sqlAddress = this.SqlAddress.Text.ToString().Trim();
            string sqlName = this.SqlName.Text.ToString().Trim();
            string sqlLogin = this.SqlLogin.Text.ToString().Trim();
            string sqlPassword = this.SqlPassword.Text.ToString().Trim();
            connectionString = string.Format(@"Data Source={0};Initial Catalog={1};User ID={2};Password={3}", sqlAddress, sqlName, sqlLogin, sqlPassword);
            DataTable dt = getDataBaseTable();
            //Thread thread = new Thread(CreateWord);
            //thread.Start(dt);
            //SqlConvert.Enabled = true;
            CreateWord(dt);
            SqlConvert.Enabled = true;
        }

        /// <summary>
        /// 获取数据库中所有表的名字
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        private DataTable getDataBaseTable()
        {
            string sql = "select name from sysobjects where xtype = 'u' and status>=0 order by name";
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(connectionString);
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand(sql, conn);
                SqlDataAdapter adapter = new SqlDataAdapter(comm);
                if (adapter != null)
                {
                    adapter.Fill(dt);
                }
                return dt;
            }
            catch (Exception ex)
            {
                titleText.Text = ex.Message;
                SqlConvert.Enabled = true;
                return dt;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
                conn.Dispose();
            }
        }

        /// <summary>
        /// 获取表字段及其字段属性
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private DataTable getTableField(string tableName)
        {
            string sql = string.Format(@"SELECT 
                                        (CASE WHEN a.colorder = 1 THEN d.name ELSE '' END) N'表名', 
                                        a.colorder N'字段序号', 
                                        a.name N'字段名', 
                                        (CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity')= 1 THEN '√'ELSE '' END) N'标识', 
                                        (CASE WHEN(SELECT COUNT(*)
                                        FROM sysobjects
                                        WHERE(name in 
                                                (SELECT name
                                                FROM sysindexes
                                                WHERE(id = a.id) AND(indid in 
                                                        (SELECT indid
                                                        FROM sysindexkeys
                                                        WHERE(id = a.id) AND(colid in 
                                                                    (SELECT colid
                                                                FROM syscolumns
                                                                WHERE(id = a.id) AND(name = a.name))))))) AND
                                            (xtype = 'PK'))> 0 THEN '√' ELSE '' END) N'主键', 
                                        b.name N'类型', 
                                        a.length N'占用字节数', 
                                        COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS N'长度', 
                                        ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS N'小数位数', 
                                        (CASE WHEN a.isnullable = 1 THEN '√'ELSE '' END) N'允许空', 
                                        ISNULL(e.text, '') N'默认值', 
                                        ISNULL(g.[value], '') AS N'字段说明'
                                    FROM syscolumns a
                                        LEFT JOIN systypes b on a.xtype = b.xusertype
                                        INNER JOIN sysobjects d on a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties'
                                        LEFT JOIN syscomments e on a.cdefault = e.id
                                        LEFT JOIN sys.extended_properties g on A.ID = G.major_id AND A.COLID = G.minor_id
                                    WHERE
                                        d.name = '{0}'--要查询的表
                                    ORDER BY
                                        object_name(a.id), a.colorder", tableName);
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection(connectionString);
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand(sql, conn);
                SqlDataAdapter adapter = new SqlDataAdapter(comm);
                if (adapter != null)
                {
                    adapter.Fill(dt);
                }
                return dt;
            }
            catch (Exception ex)
            {
                titleText.Visible = true;
                SqlConvert.Enabled = true;
                return dt;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
                conn.Dispose();
            }
        }

        /// <summary>
        /// 生成word
        /// </summary>
        /// <param name="dt"></param>
        public void CreateWord(object dtt)
        {
            DataTable dt = (DataTable)dtt;
            if (dt.Rows.Count < 1)
            {
                return;
            }
            Word.ApplicationClass app = null;
            Word.Document doc = null;
            try
            {
                
                app = new Word.ApplicationClass();
                app.Visible = true;//是否显示word
                doc = app.Documents.Add();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataTable tableDt = getTableField(dt.Rows[i][0].ToString());
                    if (tableDt.Rows.Count < 1)
                    {
                        object oEndOfDoc = "\\endofdoc";//WORD中预定义的书签
                        object missing = Type.Missing;
                        Word.Range wrdRng = doc.Bookmarks.get_Item(ref oEndOfDoc).Range;//获取当前文档的末尾位置。 
                        wrdRng.InsertAfter(" ");//插入一行
                        wrdRng = doc.Bookmarks.get_Item(ref oEndOfDoc).Range;
                        wrdRng.InsertParagraphAfter();//插入一个段落
                        Word.Table newTable = doc.Tables.Add(wrdRng, 3 + tableDt.Rows.Count, 4);
                        doc.Tables[i + 1].Cell(1, 1).Merge(doc.Tables[i + 1].Cell(1, 4));
                        doc.Tables[i + 1].Cell(2, 1).Merge(doc.Tables[i + 1].Cell(2, 4));
                        doc.Tables[i + 1].Cell(1, 1).Range.Text = dt.Rows[i][0].ToString();
                        doc.Tables[i + 1].Cell(3, 1).Range.Text = "字段名";
                        doc.Tables[i + 1].Cell(3, 2).Range.Text = "字段类型";
                        doc.Tables[i + 1].Cell(3, 3).Range.Text = "字段含义";
                        doc.Tables[i + 1].Cell(3, 4).Range.Text = "备注";
                    }
                    else
                    {
                        object oEndOfDoc = "\\endofdoc";//WORD中预定义的书签
                        object missing = Type.Missing;
                        Word.Range wrdRng = doc.Bookmarks.get_Item(ref oEndOfDoc).Range;//获取当前文档的末尾位置。 
                        wrdRng.InsertAfter(" ");//插入一行
                        wrdRng = doc.Bookmarks.get_Item(ref oEndOfDoc).Range;
                        wrdRng.InsertParagraphAfter();//插入一个段落
                        Word.Table newTable = doc.Tables.Add(wrdRng, 3 + tableDt.Rows.Count, 4);
                        doc.Tables[i + 1].Cell(1, 1).Merge(doc.Tables[i + 1].Cell(1, 4));
                        doc.Tables[i + 1].Cell(2, 1).Merge(doc.Tables[i + 1].Cell(2, 4));
                        doc.Tables[i + 1].Cell(1, 1).Range.Text = dt.Rows[i][0].ToString();
                        doc.Tables[i + 1].Cell(3, 1).Range.Text = "字段名";
                        doc.Tables[i + 1].Cell(3, 2).Range.Text = "字段类型";
                        doc.Tables[i + 1].Cell(3, 3).Range.Text = "字段含义";
                        doc.Tables[i + 1].Cell(3, 4).Range.Text = "备注";
                        for (int j = 0; j < tableDt.Rows.Count; j++)
                        {
                            string type= tableDt.Rows[j]["类型"].ToString();
                            if (type == "varchar" || type == "char" || type == "nvarchar")
                            {
                                string number = tableDt.Rows[j]["占用字节数"].ToString();
                                if (number == "-1")
                                {
                                    number = "max";
                                }
                                doc.Tables[i + 1].Cell(j + 4, 2).Range.Text = type + "(" + number + ")";
                            }
                            else
                            {
                                doc.Tables[i + 1].Cell(j + 4, 2).Range.Text = type;
                            }
                            doc.Tables[i + 1].Cell(j + 4, 1).Range.Text= tableDt.Rows[j]["字段名"].ToString();
                            doc.Tables[i + 1].Cell(j + 4, 4).Range.Text = tableDt.Rows[j]["字段说明"].ToString();
                        }
                        newTable.Select();
                        newTable.Rows.Alignment = Word.WdRowAlignment.wdAlignRowCenter;
                        newTable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleSingle;
                        newTable.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle;
                        app.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;
                    }
                   
                }
                
                string time = DateTime.Now.ToString("yyyyMMddHHmmss");
                object address =System.IO.Directory.GetCurrentDirectory()+ @"\" + time + "数据库字段表.docx";
                doc.SaveAs(ref address);
                titleText.Text = "生成成功";
            }
            catch (Exception ex)
            {
               
                titleText.Text = ex.Message;
                SqlConvert.Enabled = true;
            }
            finally
            {
                if (doc != null)
                {
                    doc.Close();
                    doc = null;
                }
                if (app != null)
                {
                    app.Quit();
                    app = null;
                }
                
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }
    }
}
